﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.IO;
using System.Xml;
using System.Xml.Linq;

namespace WcfService_Restful.DAO
{
    public class DuLieu_DAO : DataProvider
    {
        public DuLieu_DAO()
        {
            Connect();
        }

        public object ConvertDataTableToArray(DataTable dt, int i)
        {
            DTO.DuLieu_DTO data = new DTO.DuLieu_DTO();
            data.MaDuLieu = Convert.ToInt32(dt.Rows[i]["MaDuLieu"].ToString());
            data.SoNha = dt.Rows[i]["SoNha"].ToString();
            data.MaPhuong = Convert.ToInt32(dt.Rows[i]["MaPhuong"].ToString());
            data.MaQuanHuyen = Convert.ToInt32(dt.Rows[i]["MaQuanHuyen"].ToString());
            data.KinhDo = float.Parse(dt.Rows[i]["KinhDo"].ToString());
            data.ViDo = float.Parse(dt.Rows[i]["ViDo"].ToString());                        
            //data.TinhTrang = Convert.ToInt32(dt.Rows[i]["TinhTrang"].ToString());          
            return (object)data;
        }

        public DTO.DuLieu_DTO[] GetLocation()
        {
            try
            {               
                String sql = "Select * From DuLieu Where TinhTrang = 1 and MaDichVu = 20";
                DataTable dt = ExecuteQuery(sql);
                DTO.DuLieu_DTO[] ds = new DTO.DuLieu_DTO[dt.Rows.Count];
                for (int i = 0; i < dt.Rows.Count; i++)
                    ds[i] = (DTO.DuLieu_DTO)ConvertDataTableToArray(dt, i);
                Disconnect();
                return ds;
            }
            catch
            {
                Disconnect();
                return null;
            }
        }

        public DTO.DuLieu_DTO[] SearchLocation(String MaQuan, String MaPhuong)
        {
            try
            {
                String sql = "Select * From DuLieu Where TinhTrang = 1 and MaQuanHuyen = " + Convert.ToInt32(MaQuan) + " and MaPhuong = " + Convert.ToInt32(MaPhuong);
                DataTable dt = ExecuteQuery(sql);
                DTO.DuLieu_DTO[] ds = new DTO.DuLieu_DTO[dt.Rows.Count];
                for (int i = 0; i < dt.Rows.Count; i++)
                    ds[i] = (DTO.DuLieu_DTO)ConvertDataTableToArray(dt, i);
                Disconnect();
                return ds;
            }
            catch
            {
                Disconnect();
                return null;
            }
        }

        public String ReplaceString(String temp)
        {
            return temp.Replace("-", ".");
        }

        public void InsertLocation(Stream s)
        {
            try
            {
                using (XmlReader r = XmlReader.Create(s))
                {
                    XElement d = XElement.Load(r);
                    String sql = "Insert into DuLieu(MaDichVu, MaTenDiaDiem, SoNha, MaPhuong, MaQuanHuyen, MaTinhThanh, KinhDo, ViDo, TinhTrang)";
                    sql += " values(20, 3338,'" + d.Element("SoNha").Value + "'," + d.Element("MaPhuong").Value + "," + d.Element("MaQuan").Value + ", 16," + d.Element("KinhDo").Value + "," + d.Element("ViDo").Value + ", 1)";
                    ExecuteNonQuery(sql);
                    Disconnect();
                }
            }
            catch
            {
                Disconnect();
                return;
            }
        }

        public void UpdateLocation(Stream s)
        {
            try
            {
                using (XmlReader r = XmlReader.Create(s))
                {
                    XElement d = XElement.Load(r);                    
                    String sql = "Update DuLieu Set SoNha = '" + d.Element("SoNha").Value + "'";
                    sql += " Where MaDuLieu = " + d.Element("MaDuLieu").Value;
                    ExecuteNonQuery(sql);

                    sql = "Update DuLieu Set MaPhuong = " + d.Element("MaPhuong").Value;
                    sql += " Where MaDuLieu = " + d.Element("MaDuLieu").Value;
                    ExecuteNonQuery(sql);

                    sql = "Update DuLieu Set MaQuanHuyen = " + d.Element("MaQuan").Value;
                    sql += " Where MaDuLieu = " + d.Element("MaDuLieu").Value;
                    ExecuteNonQuery(sql);

                    sql = "Update DuLieu Set KinhDo = " + d.Element("KinhDo").Value;
                    sql += " Where MaDuLieu = " + d.Element("MaDuLieu").Value;
                    ExecuteNonQuery(sql);

                    sql = "Update DuLieu Set ViDo = " + d.Element("ViDo").Value;
                    sql += " Where MaDuLieu = " + d.Element("MaDuLieu").Value;
                    ExecuteNonQuery(sql);
                    Disconnect();
                }
            }
            catch
            {
                Disconnect();
                return;
            }
        }

        public void DeleteLocation(Stream s)
        {
            try
            {
                using (XmlReader r = XmlReader.Create(s))
                {
                    XElement d = XElement.Load(r);
                    String sql = "Update DuLieu Set TinhTrang = 0 Where MaDuLieu = " + d.Element("MaDuLieu").Value;
                    ExecuteNonQuery(sql);
                    Disconnect();
                }
            }
            catch
            {
                Disconnect();
                return;
            }
        }
    }
}